/****** drug ******/
/****** insert ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugInsert]
	@UnitId int,
	@Code varchar(20),
	@Name nvarchar(100),
	@Manual nvarchar(300),
	@NumberOfDay int,
	@DelFlg int
AS
BEGIN
	SET @DelFlg = 0 ;
	INSERT INTO drug([unit_id], [name], [manual], [number_of_day], [del_flg], [code])
	VALUES(@UnitId, @Name, @Manual, @NumberOfDay, @DelFlg, @Code)
END

/****** select by id ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectByID]
	@Id int
AS
BEGIN
	SELECT * 
	FROM drug
	WHERE id = @Id
END

/****** select by name ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectByName]
	@Name nvarchar(100)
AS
BEGIN
	SELECT * 
	FROM drug
	WHERE name = @Name
END

/****** select like name ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectLikeName]
	@Name nvarchar(100)
AS
BEGIN
Declare @select nvarchar(MAX);
Declare @ConWhere nvarchar(MAX);
SET @ConWhere = 'WHERE name like' + '''%' + @Name + '%''';
SET @select = '
	SELECT * 
	FROM drug';
Declare @sql nvarchar(MAX);
SET @sql = @select + @ConWhere;
EXEC SP_EXECUTESQL  @sql
END

/****** select by code ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectByCode]
	@Code nvarchar(20)
AS
BEGIN
	SELECT * 
	FROM drug
	WHERE code = @Code
END

/****** select like code ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectLikeCode]
	@Code nvarchar(20)
AS
BEGIN
Declare @select nvarchar(MAX);
Declare @ConWhere nvarchar(MAX);
SET @ConWhere = 'WHERE code like' + '''%' + @Code + '%''';
SET @select = '
	SELECT * 
	FROM drug';
Declare @sql nvarchar(MAX);
SET @sql = @select + @ConWhere;
EXEC SP_EXECUTESQL  @sql
END

/****** update by id ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugUpdateByID]
	@Id int,
	@Code varchar(20),
	@UnitId int,
	@Name nvarchar(100),
	@Manual nvarchar(300),
	@NumberOfDay int
AS
BEGIN
	UPDATE drug
	SET unit_id = @UnitId, name = @Name, manual = @Manual, number_of_day = @NumberOfDay, code = @Code
	WHERE id = @Id
END

/****** Select paging ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugSelectPaging]
@NumberPage int,
@Name nvarchar(100),
@PageSize int
AS
BEGIN
	Declare @select1 nvarchar(MAX);
	Declare @select2 nvarchar(MAX);
	Declare @con1 nvarchar(MAX);
	set @con1 ='';
	
	if(@Name != @con1)
	begin
		SET @Name = ' And Name like' + '''%' + @Name + '%''';
	end
	else
	begin
		SET @Name = ' And 1=1';
	end
	
	Set @select1 = 'SELECT *, Unit.name nameUnit  FROM
		(SELECT  ROW_NUMBER() OVER (ORDER BY E.id DESC) as MyRowNumber, *
			FROM drug E WHERE del_flg=0';
	Set @Select2 = ' ) tblEvent join Unit on tblEvent.unit_id = Unit.id
	WHERE MyRowNumber BETWEEN ( ((@NumberPage - 1) * @PageSize )+ 1) AND @NumberPage*@PageSize ';
	
	Declare @Sql nvarchar(MAX);
	
	Set @Sql = @Select1 + @Name + @Select2;
	print @sql
	EXEC SP_EXECUTESQL  @sql,
	@params = N'@NumberPage INT, @PageSize INT',
	@NumberPage=@NumberPage, @PageSize=@PageSize

END
/****** count paging ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[drugCountPaging]
@Name nvarchar(100)
AS
BEGIN
	Declare @select1 nvarchar(MAX);
	Declare @con1 nvarchar(MAX);
	set @con1 ='';
	
	if(@Name != @con1)
	begin
		SET @Name = ' And Name like' + '''%' + @Name + '%''';
	end
	else
	begin
		SET @Name = ' And 1=1';
	end
	
	Set @select1 = 'SELECT count(*)  FROM drug WHERE del_flg=0 ';
	
	Declare @Sql nvarchar(MAX);
	
	Set @Sql = @Select1 + @Name;
	print @sql
	EXEC SP_EXECUTESQL  @sql
END

/****** select unit ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[unitSelectAll]
AS
BEGIN
	SELECT * 
	FROM unit
	WHERE del_flg = 0
END

/****** insert store  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeInsert]
	@branchId int,
	@drugId int,
	@endDate date,
	@exportPrice float,
	@quantity int,
	@DelFlg int
AS
BEGIN
	SET @DelFlg = 0 ;
	INSERT INTO store([branch_Id], [drug_Id], [end_Date], [export_Price], [quantity], [del_flg])
	VALUES(@branchId, @drugId, @endDate, @exportPrice, @quantity, @DelFlg)
END

/****** update store  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeUpdateWithQuantity]
	@quantity int,
	@Id int
AS
BEGIN
	UPDATE store
	SET quantity = @quantity
	WHERE id = @Id
END

/****** select store by id ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeSelectByID]
	@Id int
AS
BEGIN
	SELECT * 
	FROM store
	WHERE id = @Id
END
/****** select store by drug id and end date ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeSelectByIdAndEndDate]
	@endDate date,
	@drugId int
AS
BEGIN
	SELECT * 
	FROM store
	WHERE end_Date = @endDate and drug_Id = @drugId
END

/****** paging store ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeSelectPaging]
@NumberPage int,
@PageSize int
AS
BEGIN
	SELECT *  FROM
		(SELECT  ROW_NUMBER() OVER (ORDER BY E.quantity DESC) as MyRowNumber, *
			FROM store E 
			WHERE E.del_flg=0 
			) as tblEvent
	WHERE MyRowNumber BETWEEN ( ((@NumberPage - 1) * @PageSize )+ 1) AND @NumberPage*@PageSize 
END

/****** count paging ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[storeCountPaging]
AS
BEGIN
	SELECT *  FROM store
		
	WHERE del_flg = 0
END

/****** insert importStore  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[importStoreInsert]
	@branchId int,
	@date date,
	@DelFlg int
AS
BEGIN
	SET @DelFlg = 0 ;
	INSERT INTO importStore([branch_Id], [date], [del_flg])
	VALUES(@branchId, @date, @DelFlg)
END

/****** insert importStore  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[importStoreInsert]
	@importStoreId int,
	@importPrice float,
	@quantity int,
	@totalPrice float,
	@DelFlg int
AS
BEGIN
	SET @DelFlg = 0 ;
	INSERT INTO importStoreDetail([importStore_Id], [import_Price], [quantity], [total_Price], [del_flg])
	VALUES(@importStoreId, @importPrice, @quantity, @totalPrice, @DelFlg)
END




